﻿namespace hedefgrup.reports.renault.database
{
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;
    using System.Diagnostics;

    public abstract class db
    {
        private static string connectionString;
        private const string insert_template = "INSERT INTO [{0}] ({1}) VALUES ({2})\r\nSELECT @@IDENTITY";

        protected db()
        {
        }

        public static long execute(string sql, params object[] args)
        {
            return execute(sql, false, args);
        }

        public static long execute(string sql, bool isScalar, params object[] args)
        {
            return execute(sql, false, 30, args);
        }

        public static long execute(string sql, bool isScalar, int executionTimeout, params object[] args)
        {
            long result = 0;

            using (SqlCommand cmd = prepareCommand(sql, args))
            {
                cmd.CommandTimeout = executionTimeout;
                if (!isScalar)
                {
                    result = cmd.ExecuteNonQuery();
                }
                else
                {
                    result = Convert.ToInt64(cmd.ExecuteScalar());
                }
                cmd.Connection.Close();
                cmd.Connection.Dispose();
            }
            return result;
        }

        private static SqlConnection getConnection()
        {
            SqlConnection connection = new SqlConnection(connectionString);
            connection.Open();
            return connection;
        }

        public static long insert(string tbl, string[] fields, object[] values)
        {
            return execute(prepare_insert(tbl, fields), true, values);
        }

        public static string prepare_insert(string tbl, string[] fields)
        {
            StringBuilder builder = new StringBuilder();
            StringBuilder builder2 = new StringBuilder();
            for (int i = 0; i < fields.Length; i++)
            {
                builder.Append("[" + fields[i] + "]" + ((i < (fields.Length - 1)) ? "," : ""));
                builder2.Append("@p_" + i + ((i < (fields.Length - 1)) ? "," : ""));
            }
            return string.Format("INSERT INTO [{0}] ({1}) VALUES ({2})\r\nSELECT @@IDENTITY", tbl, builder, builder2);
        }

        private static SqlCommand prepareCommand(string sql, params object[] args)
        {
            SqlCommand command = new SqlCommand(sql, getConnection());
            SqlParameter[] parameterArray = new SqlParameter[(args != null) ? args.Length : 0];
            for (int i = 0; i < parameterArray.Length; i++)
            {
                parameterArray[i] = new SqlParameter("@p_" + i, args[i]);
            }
            command.Parameters.AddRange(parameterArray);
            return command;
        }

        public static DataTable query(string sql, params object[] args)
        {
            Stopwatch queryTimer = Stopwatch.StartNew();
            SqlCommand cmd = prepareCommand(sql, args);
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable dataTable = new DataTable("query");

            adapter.Fill(dataTable);

            adapter.Dispose();
            cmd.Connection.Close();
            cmd.Connection.Dispose();
            cmd.Dispose();

            StringBuilder debugMessage = new StringBuilder("Sql query statement [" + sql + "] has been executed in " + queryTimer.Elapsed.TotalMilliseconds + " ms. Parameters:");
            if (args != null)
            {
                for (int i = 0; i < args.Length; i++)
                {
                    debugMessage.AppendFormat(" [{0}]", args[i]);
                }
            }
            debug.write(debugMessage.ToString(), args);

            return dataTable;
        }

        public static void setConnectionString(string databaseConnectionString)
        {
            if (string.IsNullOrEmpty(connectionString))
            {
                connectionString = databaseConnectionString;
            }
        }
    }
}

